package com.squirrel.service;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageInfo;
import com.squirrel.auxiliary.ColumnMapRowMapper;
import com.squirrel.auxiliary.IDataTypeConversion;
import com.squirrel.auxiliary.TableFieldsType;
import com.squirrel.constant.QueryCondition;
import com.squirrel.facade.DataMedium;
import com.squirrel.facade.Delete;
import com.squirrel.facade.Select;
import com.squirrel.facade.Update;
import com.squirrel.sqlbuild.ISqlbuild;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.ObjectFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


/**
 * @author wangyan
 */
public abstract class AbstractSqlHandler implements ISqlHandler {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ISqlbuild sqlbuild;
    @Autowired
    private ObjectFactory<TableFieldsType> tableFieldsType;
    @Autowired
    private IDataTypeConversion dataTypeConversion;


    //缓存tableFieldsType对象(不同数据源只需要一个)。
    private final static Map<String, TableFieldsType> tableFieldsTypeMap = new HashMap<>();

    private static final Logger log = LoggerFactory.getLogger(AbstractSqlHandler.class);

    public void setNewJdbcTemple(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int save(DataMedium dataMedium) {
        String saveSql = sqlbuild.saveSql(dataMedium.getTabName(), dataMedium);
        if (tableFieldsTypeMap.isEmpty()) {
            TableFieldsType tableFields = tableFieldsType.getObject();
            tableFields.setJdbcTemple(this.jdbcTemplate);
            tableFieldsTypeMap.put("tableFields", tableFields);
        }
        Map<String, String> columnTypeMap = tableFieldsTypeMap.get("tableFields").getColumnType(dataMedium.getTabName());
        return jdbcTemplate.update(saveSql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                dataTypeConversion.saveDataTypeConversion(ps, dataMedium, columnTypeMap);
            }
        });
    }

    @Override
    public int[][] save(List<DataMedium> dataMediaList) {
        DataMedium dataMedium = dataMediaList.get(0);
        String saveSql = sqlbuild.saveSql(dataMedium.getTabName(), dataMedium);
        if (tableFieldsTypeMap.isEmpty()) {
            TableFieldsType tableFields = tableFieldsType.getObject();
            tableFields.setJdbcTemple(this.jdbcTemplate);
            tableFieldsTypeMap.put("tableFields", tableFields);
        }
        Map<String, String> columnTypeMap = tableFieldsTypeMap.get("tableFields").getColumnType(dataMedium.getTabName());
        return jdbcTemplate.batchUpdate(saveSql, dataMediaList, 1000, new ParameterizedPreparedStatementSetter<DataMedium>() {
            @Override
            public void setValues(PreparedStatement ps, DataMedium argument) throws SQLException {
                dataTypeConversion.saveDataTypeConversion(ps, argument, columnTypeMap);
            }
        });
    }

    @Override
    public long saveAndReturnUUID(DataMedium dataMedium) {
        String saveSql = sqlbuild.saveSql(dataMedium.getTabName(), dataMedium);
        if (tableFieldsTypeMap.isEmpty()) {
            TableFieldsType tableFields = tableFieldsType.getObject();
            tableFields.setJdbcTemple(this.jdbcTemplate);
            tableFieldsTypeMap.put("tableFields", tableFields);
        }
        Map<String, String> columnTypeMap = tableFieldsTypeMap.get("tableFields").getColumnType(dataMedium.getTabName());
        long uuid = (long) dataMedium.get("UUID");
        jdbcTemplate.update(saveSql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                dataTypeConversion.saveDataTypeConversion(ps, dataMedium, columnTypeMap);
            }
        });
        return uuid;
    }

    @Override
    public int delete(Delete delete) {
        String deleteSql = sqlbuild.deleteSql(delete.getCreateDeleteSql().getTabName(), delete.getCreateDeleteSql());
        if (tableFieldsTypeMap.isEmpty()) {
            TableFieldsType tableFields = tableFieldsType.getObject();
            tableFields.setJdbcTemple(this.jdbcTemplate);
            tableFieldsTypeMap.put("tableFields", tableFields);
        }
        Map<String, String> columnTypeMap = tableFieldsTypeMap.get("tableFields").getColumnType(delete.getCreateDeleteSql().getTabName());
        return jdbcTemplate.update(deleteSql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                dataTypeConversion.deleteDataTypeConversion(ps, columnTypeMap, delete.getCreateDeleteSql());
            }
        });
    }

    @Override
    public int update(Update update) {
        String updateSql = sqlbuild.updateSql(update.getCreateUpdateSql().getDataMedium().getTabName(), update.getCreateUpdateSql());
        if (tableFieldsTypeMap.isEmpty()) {
            TableFieldsType tableFields = tableFieldsType.getObject();
            tableFields.setJdbcTemple(this.jdbcTemplate);
            tableFieldsTypeMap.put("tableFields", tableFields);
        }
        String tabName = update.getCreateUpdateSql().getDataMedium().getTabName();
        Map<String, String> columnTypeMap = tableFieldsTypeMap.get("tableFields").getColumnType(tabName);
        return jdbcTemplate.update(updateSql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                dataTypeConversion.updateDataTypeConversion(ps, update, columnTypeMap);
            }
        });
    }


    @Override
    public int[] updates(Update update) {
        CreateUpdateSql createUpdateSql = update.getCreateUpdateSql();
        List<DataMedium> dataMediums = createUpdateSql.getDataMediums();
        DataMedium dataMedium = createUpdateSql.getDataMedium();
        if (StringUtils.isEmpty(update.getTabName()) && StringUtils.isNotEmpty(dataMedium.getTabName())) {
            update.setTabName(dataMedium.getTabName());
        }
        String updateSql = sqlbuild.updateSql(update.getTabName(), update.getCreateUpdateSql());
        if (tableFieldsTypeMap.isEmpty()) {
            TableFieldsType tableFields = tableFieldsType.getObject();
            tableFields.setJdbcTemple(this.jdbcTemplate);
            tableFieldsTypeMap.put("tableFields", tableFields);
        }
        if (dataMediums.isEmpty()) {
            if (dataMedium.isEmpty()) {
                log.error("请添加更新字段");
            } else {
                dataMediums.add(dataMedium);
            }
        }
        Map<String, String> columnTypeMap = tableFieldsTypeMap.get("tableFields").getColumnType(update.getCreateUpdateSql().getDataMedium().getTabName());

        return jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                dataTypeConversion.updateDataTypeConversion(ps, update, columnTypeMap);
            }

            @Override
            public int getBatchSize() {
                return dataMediums.size();
            }
        });
    }

    @Override
    public int[][] updates(List<Update> updateList) {
        CreateUpdateSql createUpdateSql = updateList.get(0).getCreateUpdateSql();
        DataMedium dataMedium = createUpdateSql.getDataMedium();
        if (StringUtils.isEmpty(dataMedium.getTabName()) && StringUtils.isNotEmpty(dataMedium.getTabName())) {
            dataMedium.setTabName(dataMedium.getTabName());
        }
        String updateSql = sqlbuild.updateSql(dataMedium.getTabName(), createUpdateSql);
        if (tableFieldsTypeMap.isEmpty()) {
            TableFieldsType tableFields = tableFieldsType.getObject();
            tableFields.setJdbcTemple(this.jdbcTemplate);
            tableFieldsTypeMap.put("tableFields", tableFields);
        }

        Map<String, String> columnTypeMap = tableFieldsTypeMap.get("tableFields").getColumnType(dataMedium.getTabName());
        return jdbcTemplate.batchUpdate(updateSql, updateList, 1000, new ParameterizedPreparedStatementSetter<Update>() {
            @Override
            public void setValues(PreparedStatement ps, Update update) throws SQLException {
                dataTypeConversion.updateDataTypeConversion(ps, update, columnTypeMap);
            }
        });
    }

    @Override
    public boolean isDataExists(Select select) {
        CreateSelectSql createSQL = select.getCreateSQL();
        String[] fields = createSQL.getFields();
        for (String field : fields) {
            if (!field.contains("count(1)") || !field.contains("count(*)")) {
                createSQL.setQueryFields("count(1)");
            }
        }
        String querySql = sqlbuild.querySql(createSQL);
        List<Map<String, Object>> filterAndList = createSQL.getAndFilterList();
        List<Map<String, Object>> filterOrList = createSQL.getOrFilterList();
        filterAndList.addAll(filterOrList);
        if (!filterAndList.isEmpty()) {
            Iterator<Map<String, Object>> iterator = filterAndList.iterator();
            while (iterator.hasNext()) {
                Map<String, Object> next = iterator.next();
                String condition = (String) next.get("condition");
                if (QueryCondition.SQL_OMIT.equals(condition) || QueryCondition.SQL_IN.equals(condition)) {
                    iterator.remove();
                }
            }
        }
        int size = filterAndList.size();
        Object[] objects = new Object[size];
        if (!filterAndList.isEmpty()) {
            for (int i = 0; i < filterAndList.size(); i++) {
                objects[i] = filterAndList.get(i).get("value");
            }
        }
        Map<String, Object> resultMap = null;
        boolean isExist = false;
        try {
            Integer i = jdbcTemplate.queryForObject(querySql, objects, Integer.class);
            if (0 != i.intValue()) {
                isExist = true;
            }
        } catch (EmptyResultDataAccessException e) {
            return isExist;
        }
        return isExist;
    }

    @Override
    public DataMedium queryOne(Select select) {
        CreateSelectSql createSQL = select.getCreateSQL();
        String querySql = sqlbuild.querySql(createSQL);
        List<Map<String, Object>> filterAndList = createSQL.getAndFilterList();
        List<Map<String, Object>> filterOrList = createSQL.getOrFilterList();
        filterAndList.addAll(filterOrList);
        if (!filterAndList.isEmpty()) {
            Iterator<Map<String, Object>> iterator = filterAndList.iterator();
            while (iterator.hasNext()) {
                Map<String, Object> next = iterator.next();
                String condition = (String) next.get("condition");
                if (QueryCondition.SQL_OMIT.equals(condition) || QueryCondition.SQL_IN.equals(condition)) {
                    iterator.remove();
                }
            }
        }
        int size = filterAndList.size();
        Object[] objects = new Object[size];
        if (!filterAndList.isEmpty()) {
            for (int i = 0; i < filterAndList.size(); i++) {
                objects[i] = filterAndList.get(i).get("value");
            }
        }
        Map<String, Object> resultMap = null;
        try {
            resultMap = jdbcTemplate.queryForMap(querySql, objects);
        } catch (EmptyResultDataAccessException e) {
            return new DataMedium();
        }
        return new DataMedium(createSQL.getTableName(),resultMap );
    }

    @Override
    public List<DataMedium> query(Select select) {
        CreateSelectSql createSQL = select.getCreateSQL();
        String querySql = sqlbuild.querySql(createSQL);
        List<Map<String, Object>> filterAndList = createSQL.getAndFilterList();
        List<Map<String, Object>> filterOrList = createSQL.getOrFilterList();
        filterAndList.addAll(filterOrList);
        if (!filterAndList.isEmpty()) {
            Iterator<Map<String, Object>> iterator = filterAndList.iterator();
            while (iterator.hasNext()) {
                Map<String, Object> next = iterator.next();
                String condition = (String) next.get("condition");
                if (QueryCondition.SQL_OMIT.equals(condition) || QueryCondition.SQL_IN.equals(condition)) {
                    iterator.remove();
                }
            }
        }
        int size = filterAndList.size();
        Object[] objects = new Object[size];
        if (!filterAndList.isEmpty()) {
            for (int i = 0; i < filterAndList.size(); i++) {
                objects[i] = filterAndList.get(i).get("value");
            }
        }
        return jdbcTemplate.query(querySql, objects, getColumnMapRowMapper(createSQL.getTableName()));
    }

    @Override
    public PageInfo<DataMedium> query(Select select, int offset, int limit) {
        Page page = new Page<>(offset, limit);
        //PageHelper.startPage(page.getPageSize(), page.getPageNum()).doSelectPageInfo();
        return null;
    }

    @Override
    public <T> T queryForObject(Select select, Class<T> var3) {
        CreateSelectSql createSQL = select.getCreateSQL();
        String querySql = sqlbuild.querySql(createSQL);
        List<Map<String, Object>> filterAndList = createSQL.getAndFilterList();
        List<Map<String, Object>> filterOrList = createSQL.getOrFilterList();
        filterAndList.addAll(filterOrList);
        if (!filterAndList.isEmpty()) {
            Iterator<Map<String, Object>> iterator = filterAndList.iterator();
            while (iterator.hasNext()) {
                Map<String, Object> next = iterator.next();
                String condition = (String) next.get("condition");
                if (QueryCondition.SQL_OMIT.equals(condition) || QueryCondition.SQL_IN.equals(condition)) {
                    iterator.remove();
                }
            }
        }
        int size = filterAndList.size();
        Object[] objects = new Object[size];
        if (!filterAndList.isEmpty()) {
            for (int i = 0; i < filterAndList.size(); i++) {
                objects[i] = filterAndList.get(i).get("value");
            }
        }
        return jdbcTemplate.queryForObject(querySql, objects, var3);
    }

    @Override
    public <T> List<T> queryForList(Select select, Class<T> var3) {
        CreateSelectSql createSQL = select.getCreateSQL();
        String querySql = sqlbuild.querySql(createSQL);
        List<Map<String, Object>> filterAndList = createSQL.getAndFilterList();
        List<Map<String, Object>> filterOrList = createSQL.getOrFilterList();
        filterAndList.addAll(filterOrList);
        if (!filterAndList.isEmpty()) {
            Iterator<Map<String, Object>> iterator = filterAndList.iterator();
            while (iterator.hasNext()) {
                Map<String, Object> next = iterator.next();
                String condition = (String) next.get("condition");
                if (QueryCondition.SQL_OMIT.equals(condition) || QueryCondition.SQL_IN.equals(condition)) {
                    iterator.remove();
                }
            }
        }
        int size = filterAndList.size();
        Object[] objects = new Object[size];
        if (!filterAndList.isEmpty()) {
            for (int i = 0; i < filterAndList.size(); i++) {
                objects[i] = filterAndList.get(i).get("value");
            }
        }
        return jdbcTemplate.queryForList(querySql, objects, var3);
    }

    protected RowMapper<DataMedium> getColumnMapRowMapper(String tabName) {
        return new ColumnMapRowMapper(tabName);
    }

}
